package JDBCutil;
import bean.Course;
import bean.SelectCourse;
import bean.Student;
import bean.Teacher;

import java.awt.*;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


//工具类。其中包含：连接数据库、关闭资源、对数据库增删该查操作的一些通用基础的操作方法，没有限制条件。可由传入的sql语句包装该工具类，实现特定的功能。
public class JDBCUtil {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/educationalmanagementsystem?serverTimezone=UTC&useSSL=false";
        String user = "root";
        String password = "Atlantis0130";
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    //封装资源关闭的方法
    public static void closeResource(Connection connection, PreparedStatement ps){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    //封装资源关闭的方法（重载）
    public static void closeResource(Connection connection, PreparedStatement ps, ResultSet rs){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


 //              添加userlogin
 public static void AddUserLogin(int id) throws SQLException {
     Connection connection = getConnection();
     String sid=Integer.toString(id);
     PreparedStatement ps = connection.prepareStatement("INSERT INTO userlogin ( username,password,role) VALUES (?,?,?)");
     if(sid.length()==5){
         ps.setString(1,sid);
         ps.setString(2,"123");
         ps.setInt(3,2);
     }
     else if(sid.length()==4){
         ps.setString(1,sid);
         ps.setString(2,"123");
         ps.setInt(3,1);
     }
     ResultSet rs = ps.executeQuery();
     closeResource(connection, ps);
 }

    //
//                     查询所有学生
    public static List<Student> getStudentList() throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from student");
        ResultSet rs = ps.executeQuery();
        List<Student> list = new ArrayList<>();
        while(rs.next()){
            Student student = new Student();
            student.setUserid(rs.getInt("userid"));
            student.setUsername(rs.getString("username"));
            student.setSex(rs.getString("sex"));
            student.setBirthyear(rs.getDate("birthyear"));
            student.setGrade(rs.getDate("grade"));
            student.setCollege(rs.getString("college"));
            student.setSubject(rs.getString("subject"));
            student.setClasses(rs.getString("classes"));
            list.add(student);
        }
        closeResource(connection, ps);
        return list;
    }

    //          查询某个学生
    public static Student getStudent(int id) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from student where userid = ?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        Student student = new Student();
        while(rs.next()){
            student.setUserid(rs.getInt("userid"));
            student.setUsername(rs.getString("username"));
            student.setSex(rs.getString("sex"));
            student.setBirthyear(rs.getDate("birthyear"));
            student.setGrade(rs.getDate("grade"));
            student.setCollege(rs.getString("college"));
            student.setSubject(rs.getString("subject"));
            student.setClasses(rs.getString("classes"));
        }
        closeResource(connection, ps);
        return student;
    }

   //            添加学生
   public static int addStudent(Student student) throws Exception{
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "INSERT INTO student (userid, username,sex,birthyear,grade,college,subject,classes) VALUES (?,?,?,?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, student.getUserid());
            ps.setString(2, student.getUsername());
            ps.setString(3, student.getSex());
            ps.setDate(4,new java.sql.Date(student.getBirthyear().getTime()));
            ps.setDate(5,new java.sql.Date(student.getGrade().getTime()));
            ps.setString(6, student.getCollege());
            ps.setString(7, student.getSubject());
            ps.setString(8, student.getClasses());
            rowaffect = ps.executeUpdate();
            AddUserLogin(student.getUserid());
            closeResource(connection, ps);
        }

        catch (SQLException e){e.printStackTrace();}
       return rowaffect;
   }


   //          修改学生信息
   public static int modifyStudent(Student student) throws Exception{
       int rowaffect=0;
       try{
           Connection connection = getConnection();
           String sql = "UPDATE student set username=?,sex=?,birthyear=?,grade=?,college=?,subject=?,classes=? where userid = ? ";
           PreparedStatement ps = connection.prepareStatement(sql);
           ps.setString(1, student.getUsername());
           ps.setString(2, student.getSex());
           ps.setDate(3,new java.sql.Date(student.getBirthyear().getTime()));
           ps.setDate(4,new java.sql.Date(student.getGrade().getTime()));
           ps.setString(5, student.getCollege());
           ps.setString(6, student.getSubject());
           ps.setString(7, student.getClasses());
           ps.setInt(8, student.getUserid());
           rowaffect = ps.executeUpdate();
           closeResource(connection, ps);
       }
       catch (SQLException e){e.printStackTrace();}
       return rowaffect;
   }

   //   删除学生信息
    public static void deleteStudent(int[] id) throws Exception{
        try{
            for(int i=0; i<id.length; i++){
                Connection connection = getConnection();
                String sql = "delete from student where userid = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1, id[i]);
                ps.executeUpdate();
                closeResource(connection, ps);
            }
        }
        catch (SQLException e){e.printStackTrace();}

    }


    //                   查询教师信息
    public static List<Teacher> getSTeacherList() throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from teacher");
        ResultSet rs = ps.executeQuery();
        List<Teacher> list = new ArrayList<>();
        while(rs.next()){
            Teacher teacher = new Teacher();
            teacher.setUserid(rs.getInt("userid"));
            teacher.setUsername(rs.getString("username"));
            teacher.setSex(rs.getString("sex"));
            teacher.setBirthyear(rs.getDate("birthyear"));
            teacher.setGrade(rs.getDate("grade"));
            teacher.setCollege(rs.getString("college"));
            teacher.setOffice(rs.getString("office"));
            list.add(teacher);
        }
        closeResource(connection, ps);
        return list;
    }

    public static Teacher getSTeacher(int id) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from teacher where userid = ?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        Teacher teacher = new Teacher();
        while (rs.next()) {
            teacher.setUserid(rs.getInt("userid"));
            teacher.setUsername(rs.getString("username"));
            teacher.setSex(rs.getString("sex"));
            teacher.setBirthyear(rs.getDate("birthyear"));
            teacher.setGrade(rs.getDate("grade"));
            teacher.setCollege(rs.getString("college"));
            teacher.setOffice(rs.getString("office"));
        }
        closeResource(connection, ps);
        return teacher;
    }
    public static int addTeacher(Teacher teacher) throws Exception{
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "INSERT INTO teacher (userid, username,sex,birthyear,grade,college,office) VALUES (?,?,?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, teacher.getUserid());
            ps.setString(2, teacher.getUsername());
            ps.setString(3, teacher.getSex());
            ps.setDate(4,new java.sql.Date(teacher.getBirthyear().getTime()));
            ps.setDate(5,new java.sql.Date(teacher.getGrade().getTime()));
            ps.setString(6, teacher.getCollege());
            ps.setString(7, teacher.getOffice());
            rowaffect = ps.executeUpdate();
            AddUserLogin(teacher.getUserid());
            closeResource(connection, ps);
        }
        catch (SQLException e){e.printStackTrace();}
        return rowaffect;
    }

    //          修改老师信息
    public static int modifyTeacher(Teacher teacher) {
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "UPDATE teacher set username=?,sex=?,birthyear=?,grade=?,college=?,office=? where userid = ? ";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, teacher.getUsername());
            ps.setString(2, teacher.getSex());
            ps.setDate(3,new java.sql.Date(teacher.getBirthyear().getTime()));
            ps.setDate(4,new java.sql.Date(teacher.getGrade().getTime()));
            ps.setString(5, teacher.getCollege());
            ps.setString(6, teacher.getOffice());
            ps.setInt(7, teacher.getUserid());
            rowaffect = ps.executeUpdate();
            closeResource(connection, ps);
        }
        catch (SQLException e){e.printStackTrace();}
        catch (Exception e){e.printStackTrace();}
        return rowaffect;
    }

    public static void deleteTeacher(int[] id) throws Exception{
        try{
            for(int i=0; i<id.length; i++){
                Connection connection = getConnection();
                String sql = "delete from teacher where userid = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1, id[i]);
                ps.executeUpdate();
                closeResource(connection, ps);
            }
        }
        catch (SQLException e){e.printStackTrace();}

    }


//                  查询所有课程信息
    public static List<Course> getCourseList() throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from course");
        ResultSet rs = ps.executeQuery();
        List<Course> list = new ArrayList<>();
        while(rs.next()){
            Course course = new Course();
            course.setCourseid(rs.getInt("courseid"));
            course.setCoursename(rs.getString("coursename"));
            course.setTeacherid(rs.getInt("teacherid"));
            course.setCoursetime(rs.getString("coursetime"));
            course.setClassroom(rs.getString("classroom"));
            course.setCourseweek(rs.getInt("courseweek"));
            list.add(course);
        }
        closeResource(connection, ps);
        return list;
    }

    //          查询
    public static Course getCourse(int id) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from course where courseid = ?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        Course course = new Course();
        while(rs.next()){
            course.setCourseid(rs.getInt("courseid"));
            course.setCoursename(rs.getString("coursename"));
            course.setTeacherid(rs.getInt("teacherid"));
            course.setCoursetime(rs.getString("coursetime"));
            course.setClassroom(rs.getString("classroom"));
            course.setCourseweek(rs.getInt("courseweek"));
        }
        closeResource(connection, ps);
        return course;
    }

    //            添加课程信息

    public static int addCourse(Course course) throws Exception{
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "INSERT INTO course (courseid, coursename,teacherid,coursetime,classroom,courseweek) VALUES (?,?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, course.getCourseid());
            ps.setString(2, course.getCoursename());
            ps.setInt(3, course.getTeacherid());
            ps.setString(4,course.getCoursetime());
            ps.setString(5,course.getClassroom());
            ps.setInt(6, course.getCourseweek());
            rowaffect = ps.executeUpdate();
            closeResource(connection, ps);
        }
        catch (SQLException e){e.printStackTrace();}
        return rowaffect;
    }


    //          修改课程信息
    public static int modifyCourse(Course c) throws Exception{
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "UPDATE course set coursename=?,teacherid=?,coursetime=?,classroom=?,courseweek=? where courseid = ? ";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, c.getCoursename());
            ps.setInt(2,c.getTeacherid());
            ps.setString(3,c.getCoursetime());
            ps.setString(4, c.getClassroom());
            ps.setInt(5, c.getCourseweek());
            ps.setInt(6, c.getCourseid());
            rowaffect = ps.executeUpdate();
            closeResource(connection, ps);
        }
        catch (SQLException e){e.printStackTrace();}
        return rowaffect;
    }

    //   删除课程信息
    public static void deleteCourse(int[] id) throws Exception{
        try{
            for(int i=0; i<id.length; i++){
                Connection connection = getConnection();
                String sql = "delete from course where courseid = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1, id[i]);
                ps.executeUpdate();
                closeResource(connection, ps);
            }
        }
        catch (SQLException e){e.printStackTrace();}

    }


    public static List<SelectCourse> getSelectCourseList() throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from selectedcourse");
        ResultSet rs = ps.executeQuery();
        List<SelectCourse> list = new ArrayList<>();
        while(rs.next()){
            SelectCourse course = new SelectCourse();
            course.setCourseid(rs.getInt("courseid"));
            course.setStudentid(rs.getInt("studentid"));
            course.setTeacherid(rs.getInt("teacherid"));
            course.setMark(rs.getInt("mark"));
            list.add(course);
        }
        closeResource(connection, ps);
        return list;
    }

              //查询
    public static SelectCourse getSelectCourse(String num) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from selectedcourse where num = ?");
        ps.setString(1, num);
        ResultSet rs = ps.executeQuery();
        SelectCourse course = new SelectCourse();
        while(rs.next()){
            course.setCourseid(rs.getInt("courseid"));
            course.setStudentid(rs.getInt("studentid"));
            course.setTeacherid(rs.getInt("teacherid"));
            course.setMark(rs.getInt("mark"));
        }
        closeResource(connection, ps);
        return course;
    }

    //            添加选课信息

    public static int addSelectCourse(SelectCourse course) throws Exception{
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "INSERT INTO selectedcourse (courseid, studentid,teacherid,mark,num) VALUES (?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, course.getCourseid());
            ps.setInt(2, course.getStudentid());
            ps.setInt(3, course.getTeacherid());
            ps.setInt(4,course.getMark());
            ps.setString(5, course.getNum());
            rowaffect = ps.executeUpdate();
            closeResource(connection, ps);
        }
        catch (SQLException e){e.printStackTrace();}
        return rowaffect;
    }

    public static void updatenum(SelectCourse course) throws Exception{

    }


    //          修改课程信息
    public static int modifySelectCourse(SelectCourse c,String num) throws Exception{
        int rowaffect=0;
        try{
            Connection connection = getConnection();
            String sql = "UPDATE selectedcourse set courseid=?,studentid=?,teacherid=?,mark=? where num=?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, c.getCourseid());
            ps.setInt(2,c.getStudentid());
            ps.setInt(3,c.getTeacherid());
            ps.setInt(4, c.getMark());
            ps.setString(5,num);
            rowaffect = ps.executeUpdate();
            closeResource(connection, ps);
        }
        catch (SQLException e){e.printStackTrace();}
        return rowaffect;
    }

   //    删除课程信息
    public static void deleteSelectCourse(String[] num) throws Exception{
        try{
            for(int i=0; i<num.length; i++){
                Connection connection = getConnection();
                String sql = "delete from selectedcourse where num = ?";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1, num[i]);
                ps.executeUpdate();
                closeResource(connection, ps);
            }
        }
        catch (SQLException e){e.printStackTrace();}
    }



    //for student
  //获取学生已选课程
    public static List<Course> getSelectedCourseList(int id) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from selectedcourse where mark=? and studentid=?");
        ps.setInt(1,-1);
        ps.setInt(2, id);
        ResultSet rs = ps.executeQuery();
        List<Integer> idlist = new ArrayList<>();
        List<Course> list = new ArrayList<>();
        while(rs.next()){
            idlist.add(rs.getInt("courseid"));
        }
        for(int i=0; i<idlist.size(); i++){
            Course course = getCourse(idlist.get(i));
            list.add(course);
        }
        closeResource(connection, ps);
        return list;
    }
    //获取学生已修课程
    public static List<SelectCourse> getCompletedCourseList(int id) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from selectedcourse where mark<>-1 and studentid=?");
        ps.setInt(1,id);
        ResultSet rs = ps.executeQuery();
        List<SelectCourse> list = new ArrayList<>();
        while(rs.next()){
            SelectCourse course = new SelectCourse();
            course.setCourseid(rs.getInt("courseid"));
            course.setStudentid(rs.getInt("studentid"));
            course.setTeacherid(rs.getInt("teacherid"));
            course.setMark(rs.getInt("mark"));
            list.add(course);
        }
        closeResource(connection, ps);
        return list;
    }



   //     for teacher
   public static List<Course> getMyCourseList(int id) throws Exception{
       Connection connection = getConnection();
       PreparedStatement ps = connection.prepareStatement("select * from course where teacherid=?");
       ps.setInt(1,id);
       ResultSet rs = ps.executeQuery();
       List<Course> list = new ArrayList<>();
       while(rs.next()){
           Course course = new Course();
           course.setCourseid(rs.getInt("courseid"));
           course.setCoursename(rs.getString("coursename"));
           course.setTeacherid(rs.getInt("teacherid"));
           course.setCoursetime(rs.getString("coursetime"));
           course.setClassroom(rs.getString("classroom"));
           course.setCourseweek(rs.getInt("courseweek"));
           list.add(course);
       }
       closeResource(connection, ps);
       return list;
   }

    public static List<SelectCourse> getMyStudentList(int courseid,int teacherid) throws Exception{
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from selectedcourse where courseid=? and teacherid=?");
        ps.setInt(1,courseid);
        ps.setInt(2,teacherid);
        ResultSet rs = ps.executeQuery();
        List<SelectCourse> list = new ArrayList<>();
        while(rs.next()){
            SelectCourse course = new SelectCourse();
            course.setCourseid(rs.getInt("courseid"));
            course.setStudentid(rs.getInt("studentid"));
            course.setTeacherid(rs.getInt("teacherid"));
            course.setMark(rs.getInt("mark"));
            list.add(course);
        }
        closeResource(connection, ps);
        return list;
    }

    //封装查询courseinfo
//    public static CourseInfo queryForCourseInfo(String sql, Object...args) throws Exception {
//        Connection connection = getConnection();
//        PreparedStatement ps = connection.prepareStatement(sql);
//        for (int i = 0;i < args.length;i++){
//            ps.setObject(i+1,args[i]);
//        }
//        ResultSet rs = ps.executeQuery();
//        ResultSetMetaData md = rs.getMetaData();
//        int columnCount = md.getColumnCount();
//        if (rs.next()){
//            CourseInfo courseInfo = new CourseInfo();
//            for (int i = 0;i < columnCount;i++){
//                Object columnValue = rs.getObject(i + 1);
//                String columnName = md.getColumnLabel(i + 1);
//                Field field = CourseInfo.class.getDeclaredField(columnName);
//                field.setAccessible(true);
//                field.set(courseInfo,columnValue);
//            }
//            closeResource(connection,ps,rs);
//            return courseInfo;
//        }
//        closeResource(connection,ps, rs);
//        return null;
//    }

    //封装增删改操作的方法
    public static void update(String sql,Object...args) throws Exception {
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
        }
        ps.execute();
        JDBCUtil.closeResource(connection,ps);
    }

}

